#coding:utf-8

from django.db import models,connection
from utils import cache_get

class UserManager(models.Manager):
    def activity_users(self):
        def get_users():
            return self.select_related('user').order_by("-activity")[0:100]
        return cache_get("mobeir_activity_users",get_users(),60*10)
        

class TopicManager(models.Manager):
    def detail(self,tid):
       return self.extra(
          select={
            'user_image':'''
            select image from  mobeir_userprofile 
            where mobeir_topic.user_id = mobeir_userprofile.user_id
            ''',    
          }
       ).select_related('user').get(id=tid)
       
    def replies(self,id):
        return self.comments.extra(
           select={
            'user_image':'''
            select image from  mobeir_userprofile 
            where mobeir_comment.user_id = mobeir_userprofile.user_id
            ''',    
           }
        ).select_related('user').order_by('-last_update')  

    def group_topics(self,gp):
        return self.extra(
            select={'replies_count': '''
             select count(*) from mobeir_topic_comments 
             where mobeir_topic_comments.topic_id = mobeir_topic.id
             ''',
            },
       ).filter(group=gp).select_related('user').order_by('-last_update')         
       
    def my_topics(self,quser):
        return self.extra(
            select={'replies_count': '''
             select count(*) from mobeir_topic_comments 
             where mobeir_topic_comments.topic_id = mobeir_topic.id
             ''',
            },
       ).filter(user=quser).select_related('user').order_by('-last_update')
       
    def hot_topics(self):
        return self.extra(
            select={
                'replies_count': 'select count(*) from mobeir_topic_comments where mobeir_topic_comments.topic_id = mobeir_topic.id',
                },
        ).order_by('-last_update').order_by('-replies_count')[0:15]


class DeviceManager(models.Manager):         
      pass          

class AppManager(models.Manager):         
    def detail(self,user,appid):
        return self.extra(
             select={
            'inuse_count': 'select count(*) from mobeir_app_inuse where mobeir_app_inuse.app_id = mobeir_app.id',
            'want_count': 'select count(*) from mobeir_app_want where mobeir_app_want.app_id = mobeir_app.id',
            'follow_count': 'select count(*) from mobeir_app_follows where mobeir_app_follows.app_id = mobeir_app.id',
            'comments_count': 'select count(*) from mobeir_app_comments where mobeir_app_comments.app_id = mobeir_app.id',
            'likes_count': 'select count(*) from mobeir_app_likes where mobeir_app_likes.app_id = mobeir_app.id',
            'isStar':'select count(*) from mobeir_appstar where mobeir_appstar.user_id = %s and mobeir_appstar.app_id=%s'%(user.id or -1,appid),
            },
        ).get(id=appid)        
       
class CommentManager(models.Manager):
    
    def topic_replies(self,tid):
        return self.raw("""
        SELECT mc.id  as id,
               mc.content as content,
               mc.last_update as last_update,
               USER.id AS user_id,
               USER.username AS username,
               mt.id AS topic_id,
               prf.image as user_image
        FROM   mobeir_comment mc,
               mobeir_topic mt,
               mobeir_topic_comments mtc,
               auth_user USER,
               mobeir_userprofile prf
        WHERE  mc.id = mtc.comment_id
               AND mtc.topic_id = mt.id
               AND mc.user_id = USER.id
               AND USER.id = prf.user_id
               AND mt.id = %s
               order by mt.last_update desc limit 100       
        """%tid) 

 

class GroupManager(models.Manager):
    def has_user(self,gid,uid):
        cursor = connection.cursor()
        cursor.execute("""
            SELECT COUNT(*)
            FROM   mobeir_userprofile mu,
                   mobeir_group mg,
                   mobeir_userprofile_groups mug
            WHERE  mug.userprofile_id = mu.id
                   AND mug.group_id = mg.id
                   AND mg.id = %s 
                   AND mu.user_id = %s      
        """%(gid,uid))
        try:
            return (cursor.fetchone()[0] > 0)
        except:
            return False
        
        
    def my_groups(self,uid):
        return self.raw(""" 
            select mobeir_group.* from mobeir_userprofile mu,mobeir_userprofile_groups mug,mobeir_group
            where mu.user_id = %s
              and mug.group_id = mobeir_group.id
        """%uid)
        
    def all_group(self):
        return self.filter(status=True).order_by("-create_date")
           
       
        
